<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use DateTimeInterface;

class StorageManageModel extends Model
{
    use HasFactory;

    protected $table = 'storage_manage';
    protected $dateFormat = 'U';//这个时间字段被为被格式化为UNIX 时间戳的形式存储

    //toArray 时间转换问题
    protected function serializeDate(DateTimeInterface $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    //根据仓库id查询当前仓库中库存数最大的一条数据
    static public function getStorageManagerInfoByStorageId($id)
    {
        return SELF::where('storage_id', $id)
                    ->orderBy('in_storage_num', 'DESC')
                    ->first();
    }

    //库存查看列表
    static public function list($parentId, $keyword, $condition, $data)
    {
        return SELF::select(
                        'a.id',
                        'd.materiel_name',
                        'a.type_id',
                        'd.type_name',
                        'd.materiel_no',
                        'd.materiel_img',
                        'a.materiel_mean_cost',
                        'a.storage_id',
                        'e.storage_name',
                        'e.storage_address',
                        'a.in_storage_num',
                        'a.alert_num',
                        'c.id as f_class_id',
                        'c.class_name as f_class_name',
                        'c.class_no as f_class_no',
                        'b.id as s_class_id',
                        'b.class_name as s_class_name',
                        'b.class_no as s_class_no',
                        'd.remark',
                        'a.shelf_id',
                        'a.location_id',
                        's.shelf_name',
                        'l.location_name',
                    )
                    ->where(function($query) use ($keyword) {
                        $query->orWhere('b.class_name', 'like', '%'.$keyword.'%');
                        $query->orWhere('b.class_no', 'like', '%'.$keyword.'%');
                        $query->orWhere('c.class_name', 'like', '%'.$keyword.'%');
                        $query->orWhere('c.class_no', 'like', '%'.$keyword.'%');
                        $query->orWhere('d.materiel_name', 'like', '%'.$keyword.'%');
                        $query->orWhere('d.type_name', 'like', '%'.$keyword.'%');
                        $query->orWhere('d.materiel_no', 'like', '%'.$keyword.'%');

                    })
                    ->from('storage_manage as a')
                    ->leftJoin('materiel_class as b', 'a.materiel_class_id', '=', 'b.id')
                    ->leftJoin('materiel_class as c', 'c.id', '=', 'b.parent_id')
                    ->leftJoin('materiel_type as d', 'd.id', '=', 'a.type_id')
                    ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
                    ->leftJoin('storage_shelf as s','a.shelf_id','=','s.id')
                    ->leftJoin('storage_shelf_location as l','a.location_id','=','l.id')
                    ->where('a.main_id', $parentId)
                    ->where('a.delete_flag', 0)
                    ->where($condition)
                    ->orderBy('a.id', 'DESC')
                    ->paginate($data['pageSize'],['*'], '',$data['currentPage']);
    }

    //导出
    static public function export($parentId, $data)
    {
        $condition = [];

        if(!empty($data['f_class_id'])) {
            $condition['c.id'] = ['EQ' => $data['f_class_id']];
        }

        if(!empty($data['s_class_id'])) {
            $condition['a.materiel_class_id'] = ['EQ' => $data['s_class_id']];
        }

        if(!empty($data['materiel_name'])) {
             $condition['b.materiel_name'] = ['EQ' => $data['materiel_name']];
        }

        if(!empty($data['type_name'])) {
            $condition['a.type_name'] = ['EQ' => $data['type_name']];
        }

        if(!empty($data['materiel_no'])) {
            $condition['a.materiel_no'] = ['EQ' => $data['materiel_no']];
        }

        if(!empty($data['storage_id'])) {
            $condition['a.storage_id'] = ['EQ' => $data['storage_id']];
        }

        $query = StorageManageModel::select(
                        'a.id',
                        'd.materiel_name',
                        'a.type_id',
                        'd.type_name',
                        'd.materiel_no',
                        'a.materiel_mean_cost',
                        'a.storage_id',
                        'e.storage_name',
                        'e.storage_address',
                        'a.in_storage_num',
                        'a.alert_num',
                        'c.id as f_class_id',
                        'c.class_name as f_class_name',
                        'c.class_no as f_class_no',
                        'b.id as s_class_id',
                        'b.class_name as s_class_name',
                        'b.class_no as s_class_no',
                        'd.materiel_img',
                        'd.remark'
                    )
                    ->from('storage_manage as a')
                    ->leftJoin('materiel_class as b', 'a.materiel_class_id', '=', 'b.id')
                    ->leftJoin('materiel_class as c', 'c.id', '=', 'b.parent_id')
                    ->leftJoin('materiel_type as d', 'd.id', '=', 'a.type_id')
                    ->leftJoin('storage as e', 'e.id', '=', 'a.storage_id')
                    ->where($condition);

        if(!empty($data['class_no'])) {
            $keyword = empty($data['class_no'])?'':$data['class_no'];

            $query->where(function($query) use ($keyword) {
                $query->orWhere('b.class_no', 'like', '%'.$keyword.'%');
                $query->orWhere('c.class_no', 'like', '%'.$keyword.'%');
            });
        }

        if(!empty($data['max_mean_cost']) && !empty($data['min_mean_cost'])) {
            $query->whereBetween('a.materiel_mean_cost', [$data['min_mean_cost'], $data['max_mean_cost']]);
        }
        else if(!empty($data['max_mean_cost']) && empty($data['min_mean_cost'])) {
            $query->where('a.materiel_mean_cost', '<=', $data['max_mean_cost']);
        }
        else if(empty($data['max_mean_cost']) && !empty($data['min_mean_cost'])) {
            $query->where('a.materiel_mean_cost', '>=', $data['min_mean_cost']);
        }
        //
        if(!empty($data['max_storage_num']) && !empty($data['min_storage_num'])) {
            $query->whereBetween('a.in_storage_num', [$data['min_storage_num'], $data['max_storage_num']]);
        }
        else if(!empty($data['max_storage_num']) && empty($data['min_storage_num'])) {
            $query->where('a.in_storage_num', '<=', $data['max_storage_num']);
        }
        else if(empty($data['max_storage_num']) && !empty($data['min_storage_num'])) {
            $query->where('a.in_storage_num', '>=', $data['min_storage_num']);
        }

        return $query->where('a.delete_flag', 0)
                    ->where('a.main_id', $parentId)
                    ->orderBy('a.materiel_no')
                    ->get()->toArray();
    }

    //指定套餐内容对应的在库信息列表(修改套餐内容用)
    static public function packageStorageList($parentId, $packageContentInfo)
    {
        return SELF::select(
                        'a.id',
                        'a.storage_id',
                        'b.storage_name',
                        'b.storage_address',
                        'a.in_storage_num'
                    )
                    ->from('storage_manage as a')
                    ->leftJoin('storage as b', 'b.id', '=', 'a.storage_id')
                    ->where([
                        'type_id' => $packageContentInfo['type_id'],
                        'a.main_id' => $parentId,
                        'a.delete_flag' => 0
                    ])
                    // ->where('a.id', '!=', $packageContentInfo['storage_manage_id'])
                    ->get()->toArray();
    }

    static public function getInfo($className, $materielName, $typeName, $storageName, $mainId)
    {
        return StorageManageModel::select(
            'a.id',
            'a.materiel_class_id',
            'd.class_name',
            'd.class_no',
            'a.type_id',
            'c.materiel_name',
            'c.materiel_no',
            'c.type_name',
            'a.storage_id',
            'b.storage_name',
            'b.storage_address',
            'a.in_storage_num',
            'a.materiel_mean_cost',
            'a.shelf_id',
            'a.location_id',
            'a.virtual_storage_num',
        )
            ->from('storage_manage as a')
            ->leftJoin('storage as b', 'b.id', '=', 'a.storage_id')
            ->leftJoin('materiel_type as c', 'c.id', '=', 'a.type_id')
            ->leftJoin('materiel_class as d', 'd.id', '=', 'a.materiel_class_id')
            ->where('d.class_name', $className)
            ->where('c.materiel_name', $materielName)
            ->where('c.type_name', $typeName)
            ->where('b.storage_name', $storageName)
            ->where('a.main_id', $mainId)
            ->where('a.delete_flag',0)
            ->where('c.delete_flag',0)
            ->where('d.delete_flag',0)
            ->first();
    }
}
